Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
Imports System.Xml
Imports Microsoft.SqlServer.Server
Imports System.Configuration

Public Class FrmInitialSetup
    Dim DoDb As clsDatabaseManagent
    Dim SetupCompleate As Boolean = False
    Dim sqldmoApp As SQLDMO.Application
    Dim sqldmoList As SQLDMO.NameList
    Dim sqlmag As Microsoft.SqlServer.Management.Nmo.Application
    Public Dtable As DataTable
    Dim sqlApp As SQLDMO.Application = New SQLDMO.ApplicationClass()
    ' Create an Server, which resembles to your actual server
    Dim srv As SQLDMO.SQLServer2 = New SQLDMO.SQLServerClass
    ' Create Database
    Dim nDatabase As SQLDMO.Database2 = New SQLDMO.Database()
    ' Create Data Files
    Dim nDBFileData As SQLDMO.DBFile = New SQLDMO.DBFile()
    ' Create Log Files
    Dim nLogFile As SQLDMO.LogFile = New SQLDMO.LogFile()
    Dim docrypt As New Encrypt

    Private Sub SetConnectionString()
        Dim cdata As New Data
        cdata.GetConfigData()
        If cdata.UseTrust = True Then
            My.MySettings.Default("VirtualDesk.My.MySettings.db_VirtualDeskConnectionString") = cdata.TrustConnStr
        Else
            My.MySettings.Default("VirtualDesk.My.MySettings.db_VirtualDeskConnectionString") = cdata.ConnStr
        End If

        My.MySettings.Default.Save()


    End Sub
    Function getServerNames() As Boolean
        Dim dtlSQLServers As DataTable
        dtlSQLServers = SmoApplication.EnumAvailableSqlServers(False)
        Me.cboServers.Items.Clear()
        For Each drServer As DataRow In dtlSQLServers.Rows
            Me.cboServers.Items.Add(drServer("Name"))
        Next
        Me.cboServers.Text = ""
        Return True
    End Function
    Function GetDBNames() As Boolean
        '' Dim dtlSQLdb As DataTable
        Try
            If Me.chkTrusted.Checked = True Then
                srv.LoginSecure = True
                srv.Connect(Me.cboServers.Text, "", "")
            Else
                srv.Connect(Me.cboServers.Text, Me.txtUsername.Text, Me.txtPassword.Text)
            End If

            ''srv.Connect(Me.cboServers.Text, Me.txtUsername.Text, Me.txtPassword.Text)
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Virtual Desk", MessageBoxButtons.OK, MessageBoxIcon.Error)

            srv.DisConnect()
            Exit Function
        End Try

        Dim db As SQLDMO.Database
        For Each db In srv.Databases
            If db.Name <> Nothing Then
                Me.cboDataName.Items.Add(db.Name)
            End If
        Next
        cboDataName.Text = ""
        srv.DisConnect()
        ''  dtlSQLdb = SmoApplication.(False)
    End Function
    Public Function ReadAppSettings()
        Dtable = New DataTable
        Dim clm As DataColumn
        clm = New DataColumn

        clm.ColumnName = "Configuration Name"
        clm.Caption = "Configuration Name"
        Dtable.Columns.Add(clm)
        clm = New DataColumn
        clm.Caption = "Configuration Value"
        clm.ColumnName = "Configuration Value"
        Dtable.Columns.Add(clm)
        '  AppDomain.CurrentDomain.SetupInformation.ConfigurationFile 
        ' This will get the app.config file path from Current application Domain
        Dim XmlDoc As New XmlDocument()
        ' Load XML Document
        XmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile)
        ' Navigate Each xml Element of app.Config file
        Dim dr As DataRow
        For Each xElement As XmlElement In XmlDoc.DocumentElement
            If xElement.Name = "appSettings" Then

                ''Loop each node of appSettings Element 
                ' xNode.Attributes(0).Value , Mean First Attributes of Node , KeyName Portion
                ' xNode.Attributes(1).Value , Mean Second Attributes of Node,KeyValue Portion
                For Each xNode As XmlNode In xElement.ChildNodes
                    dr = Dtable.NewRow
                    dr(0) = xNode.Attributes(0).Value
                    dr(1) = xNode.Attributes(1).Value
                    Dtable.Rows.Add(dr)
                    ''MsgBox(xNode.Attributes(0).Value & ": " & xNode.Attributes(1).Value)
                    'If xNode.Attributes(0).Value = KeyName Then
                    '    xNode.Attributes(1).Value = KeyValue
                    'End If
                Next
            End If
        Next
        '' Me.dgConfiguration.DataSource = Dtable
        Return True
        ' Save app.config file
        ''XmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile)
    End Function
    Public Function UpdateAppSettings(ByVal KeyName As String, ByVal KeyValue As String)

        '  AppDomain.CurrentDomain.SetupInformation.ConfigurationFile 
        ' This will get the app.config file path from Current application Domain
        Dim XmlDoc As New XmlDocument()
        ' Load XML Document
        XmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile)
        ' Navigate Each xml Element of app.Config file
        For Each xElement As XmlElement In XmlDoc.DocumentElement
            If xElement.Name = "appSettings" Then
                ' Loop each node of appSettings Element 
                ' xNode.Attributes(0).Value , Mean First Attributes of Node , KeyName Portion
                ' xNode.Attributes(1).Value , Mean Second Attributes of Node,KeyValue Portion
                For Each xNode As XmlNode In xElement.ChildNodes
                    If xNode.Attributes(0).Value = KeyName Then
                        xNode.Attributes(1).Value = KeyValue
                    End If
                Next
            End If
        Next
        ' Save app.config file
        XmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile)
        Return True

    End Function
    Private Sub SetPermission(ByVal SQLDirectory As String)
        Dim path As String = Application.StartupPath
        Dim filepath As String = Application.StartupPath & "\VirtualDesk.exe.config"

        FileACL.AddDirectorySecurity(path, "Users", _
                       Security.AccessControl.FileSystemRights.FullControl, _
                       Security.AccessControl.AccessControlType.Allow)

        FileACL.AddFileSecurity(filepath, "Users", _
                    Security.AccessControl.FileSystemRights.FullControl, _
                    Security.AccessControl.AccessControlType.Allow)

        FileACL.AddDirectorySecurity(SQLDirectory, "Users", _
                            Security.AccessControl.FileSystemRights.FullControl, _
                            Security.AccessControl.AccessControlType.Allow)


    End Sub
    Private Sub createDB()
        If IsNothing(docrypt) = True Then
            docrypt = New Encrypt
        End If
        srv.LoginSecure = True
        srv.EnableBcp = True

        If chkTrusted.Checked = True Then
            UpdateAppSettings("UserTrustedConnection", "True")
        Else
            UpdateAppSettings("UserTrustedConnection", "False")
        End If

        UpdateAppSettings("Database", Me.cboDataName.Text.ToUpper)
        UpdateAppSettings("DatabaseUser", txtUsername.Text)
        UpdateAppSettings("DatabasePassword", docrypt.encryption(Me.txtPassword.Text))
        UpdateAppSettings("DatabaseServer", cboServers.Text)



        If Me.chkTrusted.Checked = True Then

            srv.Connect(Me.cboServers.Text, "", "")
        Else
            srv.Connect(Me.cboServers.Text, Me.txtUsername.Text, Me.txtPassword.Text)
        End If
        Dim i As Integer
        Dim doInExistDb As Boolean = False
        Dim filesreat As New FileStream(Application.StartupPath & "\database.sql", FileMode.Open)
        Dim sqlstream As New StreamReader(filesreat)
        Dim strsql As String = sqlstream.ReadToEnd
        For i = 0 To Me.cboDataName.Items.Count - 1
            If Me.cboDataName.Items(i).ToString.ToUpper = Me.cboDataName.Text.ToUpper Then
                doInExistDb = True
                Exit For
            End If
        Next
        Dim strDatabaseName As String = Me.cboDataName.Text
        If strDatabaseName = "" Then
            MessageBox.Show("Enter the Database Name")
        End If
        If doInExistDb = True Then
            Try
                Me.SetPermission(srv.Registry.SQLDataRoot & _
                      "\DATA\")
                srv.Databases.Item(strDatabaseName).ExecuteImmediate(strsql)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                srv.DisConnect()
                Return
            End Try
        Else
            Try
                Me.SetPermission(srv.Registry.SQLDataRoot & _
                       "\DATA\")
                ' Assign a name to database
                nDatabase.Name = strDatabaseName
                ' Assign a name to datafile
                nDBFileData.Name = strDatabaseName
                nDBFileData.PhysicalName = srv.Registry.SQLDataRoot & _
                       "\DATA\" + strDatabaseName + "_Data.mdf"
                nDBFileData.PrimaryFile = True
                nDBFileData.Size = 2

                nDBFileData.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB
                nDBFileData.FileGrowth = 1

                'Add the DBFile object
                nDatabase.FileGroups.Item("PRIMARY").DBFiles.Add(nDBFileData)

                ' Assign name to Log files
                nLogFile.Name = strDatabaseName + "Log"
                nLogFile.PhysicalName = srv.Registry.SQLDataRoot & _
                           "\DATA\" + strDatabaseName + "_Log.ldf"
                nLogFile.Size = 2
                nDatabase.TransactionLog.LogFiles.Add(nLogFile)

                filesreat.Close()
                sqlstream.Dispose()
                srv.Databases.Add(nDatabase)
                srv.Databases.Item(strDatabaseName).ExecuteImmediate(strsql)
                UpdateAppSettings("Configure", "False")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                srv.DisConnect()
                Return
            End Try
        End If
        Dim frmacct As New FrmAccount
        frmacct.Acct_ID = 0
        frmacct.vAddNew = True
        If frmacct.ShowDialog = Windows.Forms.DialogResult.OK Then
            SetupCompleate = True
            Me.DialogResult = Windows.Forms.DialogResult.OK
        Else
            SetupCompleate = False
            Me.DialogResult = Windows.Forms.DialogResult.No

        End If
        Me.Close()
        '<add key="Configure"  value="True"/>
        '<add key="Database" value=""/>
        '<add key="DatabaseUser" value=""/>
        '<add key="DatabasePassword" value=""/>
        '<add key="DatabaseServer" value=""/>
        '<add key="SQLServerBackupDirectory" value="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"/>
        '<add key="SQLServerDataDirectory" value="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"/>


    End Sub
    Private Sub FrmInitialSetup_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        If SetupCompleate = False Then
            Me.DialogResult = Windows.Forms.DialogResult.No
            ''  Application.Exit()
        End If
    End Sub
    Private Sub FrmInitialSetup_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ''<add name="VirtualDesk.My.MySettings.DIGI_DOCConnectionString"
        'connectionString = "Data Source=wascarsantiago\sqlexpress;Initial Catalog=DIGI_DOC;Persist Security Info=True;User ID=doc_user;Password=1q2w3e4r5t"
        'providerName="System.Data.SqlClient" />


    End Sub

    Private Sub cboServers_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboServers.Click
        If cboServers.Items.Count <= 0 Then
            Me.cboServers.Enabled = False
            Me.cboServers.Text = "Please Wait..."
            Me.cboServers.Update()
            System.Threading.Thread.Sleep(3000)
            getServerNames()
            Me.cboServers.Enabled = True
            Me.cboServers.Focus()
        End If
    End Sub


    Private Sub cboDataName_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboDataName.Click
        ' GetDBNames() 
        If cboDataName.Items.Count <= 0 Then
            Me.cboDataName.Enabled = False
            Me.cboDataName.Text = "Please Wait..."
            Me.cboDataName.Update()
            GetDBNames()
            System.Threading.Thread.Sleep(3000)
            Me.cboDataName.Enabled = True
            Me.cboDataName.Focus()
        End If
    End Sub

    Private Sub cmdConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdConnect.Click
        If Me.cboDataName.Text.Trim = "" Then
            MessageBox.Show("Invalid Database Name", "Virutal Desk", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If
        If Me.txtUsername.Text.Trim = "" Then
            MessageBox.Show("Invalid User Name", "Virutal Desk", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If
        If Me.txtPassword.Text.Trim = "" Then
            MessageBox.Show("Invalid Invalid Password", "Virutal Desk", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If
        If Me.cboServers.Text.Trim = "" Then
            MessageBox.Show("Invalid SQL Server", "Virutal Desk", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If
        createDB()
    End Sub

    Private Sub chkTrusted_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkTrusted.CheckStateChanged
        If chkTrusted.Checked = True Then
            Me.txtPassword.Enabled = False
            Me.txtPassword.Text = "none"
            Me.txtUsername.Enabled = False
            Me.txtUsername.Text = "none"
        Else
            Me.txtPassword.Enabled = True
            Me.txtPassword.Text = ""
            Me.txtUsername.Enabled = True
            Me.txtUsername.Text = ""
        End If
    End Sub

    Private Sub cboDataName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDataName.SelectedIndexChanged

    End Sub
End Class